Long running db queries and the "STOP" button

Hello list,

We are working on a project where we use PHP5 inside Apache2 and run =
very long running queries on mysql 5 and postgresql. A single query =
might run up to several minutes, and generally uses 100% CPU on the =
database server. So far everything is fine.

Now what happens, is that sometimes the user hits the stop / back button =
before the query completes and reissues a new one.
The result is that two queries are running on the database server, even =
though the results of the first query will never be used.
Furthermore as both queries are concurring for the CPU, the second query =
takes much longer than normal.

PHP will only realize that the connection was closed by the browser once =
the database query is completed, but not during the query itself.

What we are looking for is a way to cancel a running query immediately =
when the user hits the back / stop button on his browser.
We found no real solution for this problem in the usual places.=20

Any ideas, thoughts or comments are very welcome!

Cheers,
Thomas

---------------------------------------------
Thomas Seiler
Ing. sys. com. dipl. EPFL
SWISSCOM AG
Innovations
Security and Service Management
Ostermundigenstrasse 93
CH - 3050 Bern
SWITZERLAND
=A0
Phone:=A0 +41 (0)31 342 42 69
Mobile: +41 (0)79 427 97 26
Fax:  =A0 +41 (0)31 892 62 27

Thomas.Seiler@swisscom.com
http://www.swisscom.com

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Long running db queries and the "STOP" button

Few things I can think of:

1. warn the user that the query may take some time and then show a splash
type screen that indicates that something is happening

2. Run the whole thing in a new window without the toolbar

3. rework the query so it doesn't take so much time. If there are a lot of
joins in the query, you need to re-order the joins to make the combinations
more efficient, perhaps break up the queries and place the individual
results in array. Then manipulate the arrays to show the data how you want.
(I did this to great effect to get a 4 minute query down to 10 seconds)

Bastien


>From:
>To:
>Subject: [PHP-DB] Long running db queries and the "STOP" button
>Date: Thu, 27 Oct 2005 16:03:58 +0200
>
>Hello list,
>
>We are working on a project where we use PHP5 inside Apache2 and run very
>long running queries on mysql 5 and postgresql. A single query might run up
>to several minutes, and generally uses 100% CPU on the database server. So
>far everything is fine.
>
>Now what happens, is that sometimes the user hits the stop / back button
>before the query completes and reissues a new one.
>The result is that two queries are running on the database server, even
>though the results of the first query will never be used.
>Furthermore as both queries are concurring for the CPU, the second query
>takes much longer than normal.
>
>PHP will only realize that the connection was closed by the browser once
>the database query is completed, but not during the query itself.
>
>What we are looking for is a way to cancel a running query immediately when
>the user hits the back / stop button on his browser.
>We found no real solution for this problem in the usual places.
>
>Any ideas, thoughts or comments are very welcome!
>
>Cheers,
>Thomas
>
>---------------------------------------------
>Thomas Seiler
>Ing. sys. com. dipl. EPFL
>SWISSCOM AG
>Innovations
>Security and Service Management
>Ostermundigenstrasse 93
>CH - 3050 Bern
>SWITZERLAND
> 
>Phone:  +41 (0)31 342 42 69
>Mobile: +41 (0)79 427 97 26
>Fax:    +41 (0)31 892 62 27
>
>Thomas.Seiler@swisscom.com
>http://www.swisscom.com
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Long running db queries and the "STOP" button

Bastien Koert wrote:
> Few things I can think of:
>
> 1. warn the user that the query may take some time and then show a
> splash type screen that indicates that something is happening
>
> 2. Run the whole thing in a new window without the toolbar
>
> 3. rework the query so it doesn't take so much time. If there are a lot
> of joins in the query, you need to re-order the joins to make the
> combinations more efficient, perhaps break up the queries and place the
> individual results in array. Then manipulate the arrays to show the data
> how you want. (I did this to great effect to get a 4 minute query down
> to 10 seconds)

I just had the idea (never tried it myself) that you could possibly
fork the request process and let the child process perform the query
and the let the parent process (to which the browser 'is connected')
wait around for the child to finish ... outputting any results,
it could then be possible for user abort to be trapped in the parent
and them have some code that kills the child process (or possibly
that apache/php responds immeditately in the parent
and just lets the child keep running - or kills it off internally)

hope that makes sense. it does to me, but maybe I didn't explain it
well.

>
> Bastien
>
>
>> From:
>> To:
>> Subject: [PHP-DB] Long running db queries and the "STOP" button
>> Date: Thu, 27 Oct 2005 16:03:58 +0200
>>
>> Hello list,
>>
>> We are working on a project where we use PHP5 inside Apache2 and run
>> very long running queries on mysql 5 and postgresql. A single query
>> might run up to several minutes, and generally uses 100% CPU on the
>> database server. So far everything is fine.
>>
>> Now what happens, is that sometimes the user hits the stop / back
>> button before the query completes and reissues a new one.
>> The result is that two queries are running on the database server,
>> even though the results of the first query will never be used.
>> Furthermore as both queries are concurring for the CPU, the second
>> query takes much longer than normal.
>>
>> PHP will only realize that the connection was closed by the browser
>> once the database query is completed, but not during the query itself.
>>
>> What we are looking for is a way to cancel a running query immediately
>> when the user hits the back / stop button on his browser.
>> We found no real solution for this problem in the usual places.
>>
>> Any ideas, thoughts or comments are very welcome!
>>
>> Cheers,
>> Thomas
>>
>> ---------------------------------------------
>> Thomas Seiler
>> Ing. sys. com. dipl. EPFL
>> SWISSCOM AG
>> Innovations
>> Security and Service Management
>> Ostermundigenstrasse 93
>> CH - 3050 Bern
>> SWITZERLAND
>>
>> Phone: +41 (0)31 342 42 69
>> Mobile: +41 (0)79 427 97 26
>> Fax: +41 (0)31 892 62 27
>>
>> Thomas.Seiler@swisscom.com
>> http://www.swisscom.com
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Long running db queries and the "STOP" button

Hi Jochem,=20

Thanks for replying=20

> I just had the idea (never tried it myself) that you could possibly
> fork the request process and let the child process perform the query

It makes perfectly sense to us, but it seems it is only possible to fork
(pcntl_fork) with php running as CGI :(

From php.net:

arnold at helderhosting dot nl
13-Feb-2005 10:12=20
It is not possible to use the function 'pcntl_fork' when PHP is used as
Apache module. You can only use pcntl_fork in CGI mode or from
command-line.

Using this function will result in: 'Fatal error: Call to undefined
function: pcntl_fork()'


This somehow makes sense, because one would fork the entire apache
process,=20
Including all the signal handlers, open file handles for log-files and
so on...

Furthermore fork is only supported on linux/*nix but not in the windows
world, and it has to be enabled at configure time.

We have so far tried to use javascript to detect when a windows is
closed and send a second HTTP request to kill the database thread. This
however does not work reliably, because javascript has no event for the
"Stop" button of the browser :(

The next idea is to include a in output
generated by the query script. This should open a second HTTP connection
to the monitor script on most recent browsers. In this script we can
check connection_aborted() and sleep(1) in a while loop without sending
any output. If the connection was aborted, we can kill the corresponding
db thread.=20

The remaining problem with that solution is that we also have to detect
when the db query script has terminated and then exit the monitor
script. Otherwise the browser keeps loading that monitorscript and never
finishes.
This solution has two drawbacks, it work over a http proxy (these tend
to finish queries in order to cache the content) and it works only when
the browser is doing several queries in parallel.

Cheers,
Thomas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Long running db queries and the "STOP" button

Thomas.Seiler@swisscom.com wrote:
> Hi Jochem,
>
> Thanks for replying
>
>
>>I just had the idea (never tried it myself) that you could possibly
>>fork the request process and let the child process perform the query
>
>
> It makes perfectly sense to us, but it seems it is only possible to fork
> (pcntl_fork) with php running as CGI :(

oops missed that bit in the manual... still ...

if you are using apache then virtual() might offer a solution? (just guessing)

otherwise you might look into writing a little deamon (i.e. a webservice
that only your pages anbd/or scripts can access) which handles running the
queries can be polled as to the status of a query and its result (if finished) ..
such a deamon would be CLI based and could use process forking to handle multiple
requests to run long queries....

rather than use the trick you might want to look into
'AJAX' on the client side (to do the polling, start the query, etc) in order to
have more control/information on that end.

>
> From php.net:
>
> arnold at helderhosting dot nl
> 13-Feb-2005 10:12
> It is not possible to use the function 'pcntl_fork' when PHP is used as
> Apache module. You can only use pcntl_fork in CGI mode or from
> command-line.
>
> Using this function will result in: 'Fatal error: Call to undefined
> function: pcntl_fork()'
>
>
> This somehow makes sense, because one would fork the entire apache
> process,
> Including all the signal handlers, open file handles for log-files and
> so on...
>
> Furthermore fork is only supported on linux/*nix but not in the windows
> world, and it has to be enabled at configure time.
>
> We have so far tried to use javascript to detect when a windows is
> closed and send a second HTTP request to kill the database thread. This
> however does not work reliably, because javascript has no event for the
> "Stop" button of the browser :(

a daily annoyance. just like its impossible to trap when the window is actually
closed (as opposed to the document merely unloading)

>
> The next idea is to include a in output
> generated by the query script. This should open a second HTTP connection
> to the monitor script on most recent browsers. In this script we can
> check connection_aborted() and sleep(1) in a while loop without sending
> any output. If the connection was aborted, we can kill the corresponding
> db thread.
>
> The remaining problem with that solution is that we also have to detect
> when the db query script has terminated and then exit the monitor
> script. Otherwise the browser keeps loading that monitorscript and never
> finishes.
> This solution has two drawbacks, it work over a http proxy (these tend
> to finish queries in order to cache the content) and it works only when
> the browser is doing several queries in parallel.
>
> Cheers,
> Thomas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Long running db queries and the "STOP" button

Hi Jochem,=20

> if you are using apache then virtual() might offer a solution? (just
> guessing)

Cool, I didn't know of that one. But it seems that is just calls back
into apache, i.e. it doesn't generate a second independent thread. This
would have been too good to be true :)

We have since found a solution for postgres. It supports asynchronous
queries and also killing of them:

//snip ----------------------------------------------------------
// Setup query cancel handler
ignore_user_abort(false);
register_shutdown_function(cancel_query);
....
// Handler
function cancel_query() {
pg_cancel_query($dblink);
}
....
// Actual Query
pg_send_query($dblink, $query); // this is non-blocking

// This seems to be needed so that php recognizes closed connections

while(pg_connection_busy($dblink)){
usleep(500000); echo ' '; flush(); =20
}

// get the result from async query execution.=20
$result =3D pg_get_result($dblink);

//snip------------------------------------------------------ ----------

We are now looking for something similar for mysql.=20


Cheers,
Thomas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Long running db queries and the "STOP" button

Thomas.Seiler@swisscom.com wrote:
> Hi Jochem,
>
>
>>if you are using apache then virtual() might offer a solution? (just
>>guessing)
>
>
> Cool, I didn't know of that one. But it seems that is just calls back
> into apache, i.e. it doesn't generate a second independent thread. This
> would have been too good to be true :)

php engine is thread safe (in principal?) but chances are very high you
run extensions that are not. in short running php in a [true] threaded server
is only for those who enjoy a good root canal.

>
> We have since found a solution for postgres. It supports asynchronous
> queries and also killing of them:

nice feature.

>
> //snip ----------------------------------------------------------
> // Setup query cancel handler
> ignore_user_abort(false);
> register_shutdown_function(cancel_query);
> ...
> // Handler
> function cancel_query() {
> pg_cancel_query($dblink);
> }
> ...
> // Actual Query
> pg_send_query($dblink, $query); // this is non-blocking
>
> // This seems to be needed so that php recognizes closed connections
>
> while(pg_connection_busy($dblink)){
> usleep(500000); echo ' '; flush();
> }
>
> // get the result from async query execution.
> $result = pg_get_result($dblink);
>
> //snip------------------------------------------------------ ----------
>
> We are now looking for something similar for mysql.
>
>
> Cheers,
> Thomas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php